
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.SearchClub    Script Date: 2/13/2005 7:21:23 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SearchClub]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SearchClub]
GO


CREATE PROCEDURE SearchClub(
	@KeywordList NVARCHAR(255)
)AS
/******************************************************************************
**		File: 
**		Name: SearchClub
**		Desc: 
**		Return values:
**		Called by:   
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: 
**		Date: 
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/

SELECT DISTINCT [Id] INTO #ClubHitList
FROM Club
WHERE [Name] LIKE @KeywordList + '%'
OR UniqueName LIKE @KeywordList + '%'
OR Description LIKE @KeywordList + '%'

CREATE TABLE #ClubFinalHitList
( [Id]  int,
  MessageCount  int
)
DECLARE club_id_cursor CURSOR FOR
   SELECT DISTINCT [Id]
   FROM #ClubHitList

OPEN club_id_cursor
DECLARE @clubId int
DECLARE @messageCnt int

FETCH NEXT FROM club_id_cursor INTO @clubId
WHILE (@@FETCH_STATUS = 0)
BEGIN
   SELECT @messageCnt = COUNT(*) FROM Message WHERE Club_Id = @clubId
   INSERT INTO #ClubFinalHitList
   VALUES(@clubId , @messageCnt)

   FETCH NEXT FROM club_id_cursor INTO @clubId
END
CLOSE club_id_cursor
DEALLOCATE club_id_cursor

SELECT Club.*, MessageCount FROM Club, #ClubFinalHitList
WHERE Club.Id = #ClubFinalHitList.Id
ORDER BY MessageCount DESC, Club.CreateDate

DROP TABLE #ClubHitList
DROP TABLE #ClubFinalHitList


GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GRANT  EXECUTE  ON [dbo].[SearchClub]  TO [public]
GO
